# I originally found the persistant connection management code
# someplace on the mod_python mailing list and I added a few helper
# functions 

from ats.conf import settings 
from ats.cache import memcache_mgr
from ats.log import logger
import edtuple as dtuple
import MySQLdb
from MySQLdb.constants import CLIENT


# -----------------------------------------------------------------------------
# Globals
# -----------------------------------------------------------------------------
CONN = {}
cache = memcache_mgr.get_cache()
log = logger.get_logger()

# -----------------------------------------------------------------------------
# connection management
# -----------------------------------------------------------------------------
def _connect(db):
	""" Login to the database return the connection 
	"""
	conf = settings.get_config()
	cnn = MySQLdb.connect(
		host=conf.get(db, 'host'), 
		user=conf.get(db, 'user'), 
		passwd=conf.get(db, 'passwd'), 
		db=conf.get(db,'database'),
		client_flag=CLIENT.MULTI_STATEMENTS)
	cnn.autocommit(1)
	return cnn

# -----------------------------------------------------------------------------
def _get_conn(db, relogin = 0):
	""" Returns a persistent connection to the database  
		attempts to reconnect if the connection is lost.
	"""
	global CONN
	if relogin:
		CONN[db] = _connect(db) 
		return CONN[db]
	else:
		if CONN.has_key(db) and CONN[db]:
			return CONN[db]
		else:
			CONN[db] = _connect(db) 
			return CONN[db]

# -----------------------------------------------------------------------------
# execute sql
# -----------------------------------------------------------------------------
def _exec_stmt(db, stmt):
	""" Runs SQL statement and returns (count, curs) 
	"""
	cnn, curs = None, None
	try:
		cnn = _get_conn(db)
		curs = cnn.cursor()
		count = curs.execute(stmt)
	except MySQLdb.OperationalError, e: 
		cnn = _get_conn(db, relogin=1)
		curs = cnn.cursor()
		count = curs.execute(stmt)
	return count, curs

# -----------------------------------------------------------------------------
# database access functions
# -----------------------------------------------------------------------------
def close(db):
	global CONN
	if CONN.has_key(db):
		cnn = CONN[db]
		del CONN[db]
		try:
			cnn.close()
		except:
			pass

# -----------------------------------------------------------------------------
def get_first_record(db, stmt=None, ttl=0, force=0):
	""" Runs select statement and returns 1st record 
	"""
	rec = get_records(db, stmt, ttl, force)
	if rec[0] >= 1: return rec[1][0]
	return None

# -----------------------------------------------------------------------------
def get_records(db, stmt=None, ttl=0, force=0):
	""" Runs select statement and returns a list of the form:
		(record count, result set) 
	"""
	global cache, log
	if stmt == None: 
		return None
	# check cache
	if ttl > 0 and force == 0:
		tmp = cache.get(hash("%s:%s" % (db, stmt)))
		if tmp and len(tmp) == 2:
			return tmp
	# cahce miss or ttl == 0 or force is true
	count, curs = _exec_stmt(db, stmt)
	recset = None
	try:
		recset = map(
			lambda x: 
				dtuple.DatabaseTuple(
					dtuple.TupleDescriptor(curs.description),
					x),
				curs.fetchall())	
	finally: curs.close()
	# add to cache
	if ttl > 0:
		cache.set(hash("%s:%s" % (db, stmt)), (count, recset), ttl*60)
	return count, recset

# -----------------------------------------------------------------------------
def do_insert(db, stmt=None):
	""" Runs insert statement and returns the id of the newly 
		inserted record or -1 on error. 
	"""
	if stmt == None:
		return -1
	count, recset = get_records(db, stmt)
	if count <= 0:
		return -1
	return recset[0][0]
	
# -----------------------------------------------------------------------------
def do_update(db, stmt=None):
	""" Runs update statement and returns the number of records 
		updated or -1 on failure. 
	"""
	if stmt == None:
		return -1
	count, curs = -1, None
	try: count, curs = _exec_stmt(db, stmt)
	finally: 
		if curs: curs.close()
	return count 
	
# -----------------------------------------------------------------------------
def do_delete(db, stmt=None):
	""" Runs delete statement and returns the number of records 
		deleted or -1 on failure. 
	"""
	return do_update(db, stmt) # same impl as update	

# -----------------------------------------------------------------------------
# escape functions
# -----------------------------------------------------------------------------
def real_escape(db, str=""):
	""" mysql_real_escape_string
	"""
	if type(str) == type("abc"):
		try:
			return _get_con(db).escape_string(str)
		except: 
			pass
	return str

# -----------------------------------------------------------------------------
def escape(str=""):
	""" Escapes a string for DB calls
	"""
	return MySQLdb.escape_string(str)
